package com.jtny.nytb.config.excel.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.Comparator;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

public class ExcelUtil {

//    public static <T> void uploadExcelAboutUser(HttpServletResponse response, String fileName, List<T> dataList, Class<T> cls){
//        //声明输出流
//        OutputStream os = null;
//        //设置响应头
//        setResponseHeader(response,fileName);
//        try {
//            Field[] fields = cls.getDeclaredFields();
//            List<Field> fieldList = Arrays.stream(fields)
//                    .filter(field -> {
//                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
//                        if (annotation != null && annotation.col() > 0) {
//                            field.setAccessible(true);
//                            return true;
//                        }
//                        return false;
//                    }).sorted(Comparator.comparing(field -> {
//                        int col = 0;
//                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
//                        if (annotation != null) {
//                            col = annotation.col();
//                        }
//                        return col;
//                    })).collect(Collectors.toList());
//
//            //获取输出流
//            os = response.getOutputStream();
//            //内存中保留1000条数据，以免内存溢出，其余写入硬盘
//            //SXSSFWorkbook wb = new SXSSFWorkbook(1000);
//            HSSFWorkbook wb = new HSSFWorkbook();
//            //获取该工作区的第一个sheet
//            Sheet sheet1 = wb.createSheet("sheet1");
//
//            CellStyle cellStyle = wb.createCellStyle();
//            //设置水平居中
//            cellStyle.setAlignment(HorizontalAlignment.CENTER);
//            //设置垂直居中
//            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//            Font font = wb.createFont();
//            cellStyle.setFont(font);
//            cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
//            for (int i = 0; i < dataList.size(); i++) {
//                AtomicInteger ai = new AtomicInteger();
//                {
//                    Row row = sheet1.createRow(ai.getAndIncrement());
//                    AtomicInteger aj = new AtomicInteger();
//                    //写入头部
//                    fieldList.forEach(field -> {
//                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
//                        String columnName = "";
//                        if (annotation != null) {
//                            columnName = annotation.value();
//                        }
//                        Cell cell = row.createCell(aj.getAndIncrement());
//
//                        //CellStyle cellStyle = wb.createCellStyle();
//                        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//
//                        //Font font = wb.createFont();
//                        cellStyle.setFont(font);
//                        cell.setCellStyle(cellStyle);
//                        cell.setCellValue(columnName);
//                    });
//                }
//                //设置内容行
//                if (!CollectionUtils.isEmpty(dataList)) {
//                    dataList.forEach(t -> {
//                        Row row1 = sheet1.createRow(ai.getAndIncrement());
//                        AtomicInteger aj = new AtomicInteger();
//                        fieldList.forEach(field -> {
//                            //Class<?> type = field.getType();
//                            Object value = "";
//                            try {
//                                //value = field.get(t);
//                                value = t.getClass().getMethod("get" + initStr(field.getName())).invoke(t);
//                            } catch (Exception e) {
//                                e.printStackTrace();
//                            }
//                            Cell cell = row1.createCell(aj.getAndIncrement());
//                            cell.setCellStyle(cellStyle);
//                            if (value != null) {
//                                cell.setCellValue(value.toString());
//                            }
//                        });
//                    });
//                }
//                for (int x = 0; x < fieldList.size(); x++) {
//                    sheet1.autoSizeColumn(x);
//                    sheet1.setColumnWidth(x,sheet1.getColumnWidth(x)*17/10);
//                }
//            }
//            //将整理好的excel数据写入流中
//            wb.write(os);
//        } catch (IOException e) {
//            e.printStackTrace();
//        } finally {
//            try {
//                // 关闭输出流
//                if (os != null) {
//                    os.close();
//                }
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//        }
//    }
//
//    /*
//        设置浏览器下载响应头
//     */
//    private static void setResponseHeader(HttpServletResponse response, String fileName) {
//        try {
//            try {
//                fileName = new String(fileName.getBytes(),"ISO8859-1");
//            } catch (UnsupportedEncodingException e) {
//                e.printStackTrace();
//            }
//            response.setContentType("application/octet-stream;charset=UTF-8");
//            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
//            response.addHeader("Pargam", "no-cache");
//            response.addHeader("Cache-Control", "no-cache");
//        } catch (Exception ex) {
//            ex.printStackTrace();
//        }
//    }
//
//    private static String initStr(String old){  // 将单词的首字母大写
//        String str = old.substring(0,1).toUpperCase() + old.substring(1) ;
//        return str ;
//    }

    public static <T> void uploadExcelAboutUser(HttpServletResponse response, String fileName, List<T> dataList, Class<T> cls, String genderFieldName) {
        // Declare output stream
        OutputStream os = null;
        // Set response header
        setResponseHeader(response, fileName);
        try {
            Field[] fields = cls.getDeclaredFields();
            List<Field> fieldList = Arrays.stream(fields)
                    .filter(field -> {
                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                        if (annotation != null && annotation.col() > 0) {
                            field.setAccessible(true);
                            return true;
                        }
                        return false;
                    }).sorted(Comparator.comparing(field -> {
                        int col = 0;
                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                        if (annotation != null) {
                            col = annotation.col();
                        }
                        return col;
                    })).collect(Collectors.toList());

            // Get output stream
            os = response.getOutputStream();
            // Create workbook
            HSSFWorkbook wb = new HSSFWorkbook();
            // Create sheet
            Sheet sheet1 = wb.createSheet("sheet1");

            CellStyle cellStyle = wb.createCellStyle();
            // Set alignment
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // Set vertical alignment
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Font font = wb.createFont();
            cellStyle.setFont(font);
            cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
            for (int i = 0; i < dataList.size(); i++) {
                AtomicInteger ai = new AtomicInteger();
                {
                    Row row = sheet1.createRow(ai.getAndIncrement());
                    AtomicInteger aj = new AtomicInteger();
                    // Write headers
                    fieldList.forEach(field -> {
                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                        String columnName = "";
                        if (annotation != null) {
                            if (field.getName().equals(genderFieldName)) {
                                columnName = "性别";
                            } else {
                                columnName = annotation.value();
                            }
                        }
                        Cell cell = row.createCell(aj.getAndIncrement());

                        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());

                        cellStyle.setFont(font);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(columnName);
                    });
                }
                // Set content rows
                if (!CollectionUtils.isEmpty(dataList)) {
                    dataList.forEach(t -> {
                        Row row1 = sheet1.createRow(ai.getAndIncrement());
                        AtomicInteger aj = new AtomicInteger();
                        fieldList.forEach(field -> {
                            Object value = "";
                            try {
                                value = t.getClass().getMethod("get" + initStr(field.getName())).invoke(t);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            Cell cell = row1.createCell(aj.getAndIncrement());
                            cell.setCellStyle(cellStyle);
                            if (field.getName().equals(genderFieldName)) {
                                if (value != null && value instanceof Integer) {
                                    if ((Integer) value == 0) {
                                        value = "男";
                                    } else if ((Integer) value == 1) {
                                        value = "女";
                                    }
                                }
                            }
                            if (value != null) {
                                cell.setCellValue(value.toString());
                            }
                        });
                    });
                }
                for (int x = 0; x < fieldList.size(); x++) {
                    sheet1.autoSizeColumn(x);
                    sheet1.setColumnWidth(x, sheet1.getColumnWidth(x) * 17 / 10);
                }
            }
            // Write the excel data to the output stream
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                // Close the output stream
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /*
        Set response headers for browser download
    */
    private static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private static String initStr(String old) {
        String str = old.substring(0, 1).toUpperCase() + old.substring(1);
        return str;
    }

}
